# -*- coding: utf-8 -*-
#!/usr/bin/python

##copyright 2014 Sebastian Stetter
##This file is part of the Lemonade Small Business Suite project and released under GPL v2
##http://lemonade.sebastianstetter.de

from __future__ import division
import datetime,  getpass
from dateutil.relativedelta import relativedelta
from sqlalchemy.schema import Column
import logging
logging.basicConfig( level = logging.DEBUG )
logger = logging.getLogger( 'model' )
import sqlalchemy.types

from camelot.core.utils import ugettext_lazy as _
from camelot.core.utils import ugettext
from camelot.view.utils import text_from_richtext
from camelot.admin.entity_admin import EntityAdmin
from camelot.admin.object_admin import ObjectAdmin
from camelot.core.orm import Entity
from camelot.core.orm.relationships import *
from camelot.core.orm.options import using_options
import camelot.types
from camelot.view import forms
from camelot.view.art import ColorScheme
from camelot.view.controls import delegates
from camelot.admin.action.form_action import CloseForm
from camelot.admin.action.application_action import Refresh
from jinja2 import Environment
from actions import *
from validators import *
from choices import *

class TargetList(Entity):
    __tablename__ = 'target_list'
    name = Column(sqlalchemy.types.Unicode(100),  nullable=False)
    user = ManyToOne('User',  backref='targetlists')
    description = Column(camelot.types.RichText())
    last_updated = Column(sqlalchemy.types.DateTime(),  default=datetime.datetime.now())
    customers = ManyToMany('Customer',  inverse='targetlists',  tablename='target_list_customers')
    
#        ###########################
#    # properties for csv export#
#    @property
#    def csv_test(self):
#        test=u"Hallo CSV_Welt!"
#        return test
#    
#    ###########################

    
    def __init__(self):
        """set the user to the currntly logged in user"""
        super(TargetList, self).__init__()
        myuser = User.query.filter_by(username=getpass.getuser()).first()
        if myuser:
            self.user=myuser
            
    def __unicode__(self):
        return self.name or u'unknown traget list'
    
    class Admin(EntityAdmin):
        #from camelot.admin.table import ColumnGroup 
        verbose_name=_('Target list')
        list_display=['name','user','last_updated', 'description']
#        list_display=[
#                      ColumnGroup( 'Standard', ['name','user','last_updated', 'description' ]),
#                      ColumnGroup( 'CSV-Export', ['csv_test','name','user','last_updated', 'description' ]),
#                      ]
        
        form_display=list_display+['customers']

    
    
    
class Customer(Entity):
    __tablename__ = 'customer'
    
    customer_number = ManyToOne('CustomerNumber',  inverse = 'customers')
    user = ManyToOne('User')
    matchcode = Column(sqlalchemy.types.Text())
    
    company_name = Column(sqlalchemy.types.Unicode(100))
    company_additional = Column(sqlalchemy.types.Unicode(100))
    company_legal_form = Column(sqlalchemy.types.Unicode(100))
    company_logo=Column(camelot.types.Image())
    
    person_salutation = Column(sqlalchemy.types.Unicode(100))
    person_letter_salutation = Column(sqlalchemy.types.Unicode(110))
    person_firstname = Column(sqlalchemy.types.Unicode(100))
    person_initials = Column(sqlalchemy.types.Unicode(100))
    person_lastname = Column(sqlalchemy.types.Unicode(100))
    person_nickname = Column(sqlalchemy.types.Unicode(100))
    person_spouses_name = Column(sqlalchemy.types.Unicode(200))
    person_birthday = Column(sqlalchemy.types.Date())
    person_picture=Column(camelot.types.Image())
    person_language=Column(camelot.types.Language(),  default=u'de_DE')
    person_profession = Column(sqlalchemy.types.Unicode(100))
    person_degree = Column(sqlalchemy.types.Unicode(100))    
    person_university = Column(sqlalchemy.types.Unicode(100))    
    person_department = Column(sqlalchemy.types.Unicode(100))    
    
    person_debuty = ManyToOne('Customer',  colname='person_debuty_id')
    person_manager = ManyToOne('Customer',  colname='person_manager_id')
    person_assistant = ManyToOne('Customer',  colname='person_assistant_id')
    
    #contact mechanisms
    contact_mechanisms = OneToMany("ContactMechanism", cascade='all, delete, delete-orphan',  single_parent=True)
    
    #financial stuff
    payment_rating=Column(camelot.types.Rating(),  default=3)
    
    iban = Column(sqlalchemy.types.Unicode(40))
    bic = Column(sqlalchemy.types.Unicode(20))
    bank_name = Column(sqlalchemy.types.Unicode(100))
    
    vat_id = Column(sqlalchemy.types.Unicode(100))
    last__time_vat_id_check_ok = Column(sqlalchemy.types.Date())
    discount = Column(sqlalchemy.types.Float(), default=0.00)
    
    notes=Column(camelot.types.RichText())

    payment_term = ManyToOne('PaymentTerm',  backref='customers')
    industry = ManyToOne('Industry',  backref='customers')
    
    supplyer_id = Column(sqlalchemy.types.Unicode(100)) 
    locked = Column(sqlalchemy.types.Boolean(),  default=False)
    
    #other relations
    
    categories = ManyToMany('Category', tablename = 'customer_categories', local_colname = 'customer.id', remote_colname = 'category.id' )
    tags= ManyToMany('Tag', tablename = 'customer_tags',local_colname = 'customer.id', remote_colname = 'tag.id' )
    targetlists=ManyToMany('TargetList',  inverse='customers')

    
    #prefered_phone = ManyToOne("ContactMechanism", backref='prefered_phone',  use_alter=True )
    @property
    def prefered_phone(self):
        preferedlist = [m for m in self.contact_mechanisms if m.mechanism[0] == 'phone' and m.is_prefered == True]
        if len(preferedlist) >0:
            return preferedlist[0]
        else:
            return None
    
#    prefered_email = ManyToOne("ContactMechanism", backref='prefered_email',  use_alter=True)
    @property
    def prefered_email(self):
        preferedlist = [m for m in self.contact_mechanisms if m.mechanism[0] == 'email' and m.is_prefered == True]
        if len(preferedlist) >0:
            return preferedlist[0]
        else:
            return None
    
    
#    prefered_website = ManyToOne("ContactMechanism", backref='prefered_website',  use_alter=True)
    @property
    def prefered_website(self):
        preferedlist = [m for m in self.contact_mechanisms if m.mechanism[0] == 'website' and m.is_prefered == True]
        if len(preferedlist) >0:
            return preferedlist[0]
        else:
            return None
    
    
#    prefered_social    = ManyToOne("ContactMechanism", backref='prefered_social',  use_alter=True)
    @property
    def prefered_social(self):
        preferedlist = [m for m in self.contact_mechanisms if m.mechanism[0] == 'social' and m.is_prefered == True]
        if len(preferedlist) >0:
            return preferedlist[0]
        else:
            return None
            
    @property
    def prefered_fax(self):
        preferedlist = [m for m in self.contact_mechanisms if m.mechanism[0] == 'fax' and m.is_prefered == True]
        if len(preferedlist) >0:
            return preferedlist[0]
        else:
            return None
            
    @property
    def prefered_mobile(self):
        preferedlist = [m for m in self.contact_mechanisms if m.mechanism[0] == 'mobile' and m.is_prefered == True]
        if len(preferedlist) >0:
            return preferedlist[0]
        else:
            return None
    
    
    @property
    def prefered_im(self):
        preferedlist = [m for m in self.contact_mechanisms if m.mechanism[0] == 'im' and m.is_prefered == True]
        if len(preferedlist) >0:
            return preferedlist[0]
        else:
            return None
            
    @property
    def prefered_pager(self):
        preferedlist = [m for m in self.contact_mechanisms if m.mechanism[0] == 'pager' and m.is_prefered == True]
        if len(preferedlist) >0:
            return preferedlist[0]
        else:
            return None
    
    #addresses
    addresses= OneToMany('Address',  inverse='customer')
    
    @property
    def prefered_postal_address(self):
        a = [a for a in self.addresses if a.is_prefered_address==True][0]
        return a
        
    @property
    def billing_address(self):
        a = [a for a in self.addresses if a.is_billing_address==True][0]
        return a
    @property
    def shipping_address(self):
        a = [a for a in self.addresses if a.is_shipping_address==True][0]        
        return a
        
#additional properties to make the important addresses 'flat' in order to be exported more easily
    @property
    def prefered_adr_street_box(self):
        if self.prefered_postal_address:
            return self.prefered_postal_address.street_box
        
    @property
    def prefered_adr_additional_adr_info(self):
        if self.prefered_postal_address:
            return self.prefered_postal_address.additional_address_info
    
    @property
    def prefered_adr_zip(self):
        if self.prefered_postal_address:
            return self.prefered_postal_address.zip
        
    @property    
    def prefered_adr_city(self):
        if self.prefered_postal_address:
            return self.prefered_postal_address.city
        
    @property
    def prefered_adr_counry(self):
        if self.prefered_postal_address:
            return self.prefered_postal_address.country
        
    @property
    def prefered_adr_region(self):
        if self.prefered_postal_address:
            return self.prefered_postal_address.region
        
    @property
    def prefered_adr_comment(self):
        if self.prefered_postal_address:
            return self.prefered_postal_address.comment
        
    @property
    def prefered_adr_type(self):
        if self.prefered_postal_address:
            return self.prefered_postal_address.type

#billing
    @property
    def billing_adr_street_box(self):
        if self.billing_address:
            return self.billing_address.street_box
        
    @property
    def billing_adr_additional_adr_info(self):
        if self.billing_address:
            return self.billing_address.additional_address_info
    
    @property
    def billing_adr_zip(self):
        if self.billing_address:
            return self.billing_address.zip
        
    @property    
    def billing_adr_city(self):
        if self.billing_address:
            return self.billing_address.city
        
    @property
    def billing_adr_counry(self):
        if self.billing_address:
            return self.billing_address.country
        
    @property
    def billing_adr_region(self):
        if self.billing_address:
            return self.billing_address.region
        
    @property
    def billing_adr_comment(self):
        if self.billing_address:
            return self.billing_address.comment
        
    @property
    def billing_adr_type(self):
        if self.billing_address:
            return self.billing_address.type

#Shipping
    @property
    def shipping_adr_street_box(self):
        if self.shipping_address:
            return self.shipping_address.street_box
        
    @property
    def shipping_adr_additional_adr_info(self):
        if self.shipping_address:
            return self.shipping_address.additional_address_info
    
    @property
    def shipping_adr_zip(self):
        if self.shipping_address:
            return self.shipping_address.zip
        
    @property    
    def shipping_adr_city(self):
        if self.shipping_address:
            return self.shipping_address.city
        
    @property
    def shipping_adr_counry(self):
        if self.shipping_address:
            return self.shipping_address.country
        
    @property
    def shipping_adr_region(self):
        if self.shipping_address:
            return self.shipping_address.region
        
    @property
    def shipping_adr_comment(self):
        if self.shipping_address:
            return self.shipping_address.comment
        
    @property
    def shipping_adr_type(self):
        if self.shipping_address:
            return self.shipping_address.type
        
    
    def __init__(self):
        """set the user to the currntly logged in user"""
        super(Customer, self).__init__()
        myuser = User.query.filter_by(username=getpass.getuser()).first()
        if myuser:
            self.user=myuser
        
    def on_close(self):
        """This method is called by our customized lemonade.actions.CloseFormAction()"""
        pass
    
    @property
    def notice(self):
        '''Check if a person with this name combination already exists and if the customer is locked'''
        message = u""
        for person in self.__class__.query.filter_by(person_firstname=self.person_firstname, person_lastname=self.person_lastname):
            if person != self:
                message=u'A person with the same name already exists!\n'
            if self.locked:
                message = message + u'This customer is marked as locked in the "Financial" tab!'
                message = _(message)
            if message == u"":
                message = None
        return message
    

    def __unicode__(self):
        return self.matchcode or u'unknown customer'
        
    class Admin(EntityAdmin):
        verbose_name = 'Customer'
        list_display = [
                            'matchcode', 
                            'customer_number', 
                            'user', 
                            'company_name', 
                            'company_additional', 
                            #FIXME: just retrun the actual addresses here
                            'person_firstname', 
                            'person_lastname', 
                            'prefered_email', 
                            'prefered_phone', 
                            'prefered_mobile', 
                            'prefered_im', 
                            'prefered_fax', 
                            'prefered_pager', 
                            'prefered_website', 
                            'prefered_social', 

                            'billing_adr_street_box', 
                            'billing_adr_additional_adr_info', 
                            'billing_adr_city', 
                            'billing_adr_zip', 
                            'billing_adr_region', 
                            'billing_adr_counry', 
                            'billing_adr_type', 
                            'billing_adr_comment', 
                            
                            'shipping_adr_street_box', 
                            'shipping_adr_additional_adr_info', 
                            'shipping_adr_city', 
                            'shipping_adr_zip', 
                            'shipping_adr_region', 
                            'shipping_adr_counry', 
                            'shipping_adr_type', 
                            'shipping_adr_comment', 

                            'prefered_adr_street_box', 
                            'prefered_adr_additional_adr_info', 
                            'prefered_adr_city', 
                            'prefered_adr_zip', 
                            'prefered_adr_region', 
                            'prefered_adr_counry', 
                            'prefered_adr_type', 
                            'prefered_adr_comment', 
                            
                            #
                            ]
        list_filter = []
        #fixme: gui problem with delete confirmation dialog - something wit unexpected type unicode???
        delete_mode='on_confirm'
        form_size=(1350, 800)
        form_actions=[CallPreferedPhone(),  SendEmail(),  SendStandardEmail(), SendInfoPackage(), SendLetter()]
        #list_actions=[CallPreferedPhone(), CallPreferedMobile() ,  SendEmail(),  SendStandardEmail1(), SendStandardEmail2(),  SendStandardEmail3(), SendInfoPackage(), SendLetter()]
        field_attributes = dict( 
                                notice=dict(delegate = delegates.NoteDelegate,  name=''), 
                                person_picture = dict(name=''), 
                                company_logo = dict(name=''), 
                                )
        form_display = [forms.VBoxForm([
                                [
                                  #vertikal oben
                                   forms.HBoxForm([ ['matchcode'],['user'],['notice']])
                                  ], 
                                [
                                    #vertikal mitte1
                                    forms.HBoxForm([
                                                         [
                                                             #links
                                                             forms.TabForm( 
                                                                 [
                                                                 (_('Personal'), ['person_picture','person_salutation','person_letter_salutation', 'person_firstname', 'person_initials' ,'person_lastname', 'person_language','person_birthday', 'person_nickname', 'person_spouses_name' , forms.Stretch() ]), 
                                                                 (_('Professional'),  [ 'person_profession','person_degree','person_university','person_department','person_debuty','person_manager','person_assistant', forms.Stretch()]), 
                                                                 (_('Organization'), ['company_logo', 'company_name', 'company_additional', 'company_legal_form', 'industry','customer_number' ,'supplyer_id',  forms.Stretch() ]), 
                                                                 (_('Financial'),  ['payment_rating','locked','bank_name','iban','bic','vat_id', 'last__time_vat_id_check_ok','discount', 'payment_term', forms.Stretch()]), 
                                                                 ]
                                                                 ) 
                                                            ],
                                                         [
                                                             #rechts
                                                             forms.TabForm( 
                                                                 [
                                                                 (_('Contact mechanisms'),  ['contact_mechanisms', ]),
                                                                 #(_('Prefered Contact mechanisms'),  ['prefered_email','prefered_fax', 'prefered_mobile',  'prefered_phone', 'prefered_pager',  'prefered_social',  'prefered_website',  'prefered_im' ]),
                                                                 (_('Pysical addresses'),  ['addresses'], )
                                                                 ]
                                                             
                                                             ), 
                                                              forms.TabForm( 
                                                                 [
                                                                 (_('Categories'),  ['categories']), 
                                                                 (_('Tags'),  ['tags']), 
                                                                 (_('Target lists'),  ['targetlists'])
                                                                 ]
                                                             )
                                                         ]
                                                 ])
                                ],

                                [
                                    #vertikal unten
                                    forms.HBoxForm([
                                                   forms.TabForm( 
                                                                 [
                                                                 (_('Contact history'),  ['contacts', forms.Stretch()]),
                                                                 (_('Sales Oportunities'),  ['opportunities', forms.Stretch()]), 
                                                                 (_('Vouchers'),  ['vouchers', forms.Stretch()]),
                                                                 (_('Documents'),  ['documents', forms.Stretch()]), 
                                                                 (_('Notes'),  ['notes', forms.Stretch()])   
                                                                 ]
                                                             ), 
                                                              forms.Stretch()
                                                         ])
                                ]
                            ]), 
                            forms.Stretch()
                                                    ]
                                                    



class CustomerNumber(Entity):
    __tablename__ = 'customer_number'
    
    customers = OneToMany('Customer',  inverse='customer_number')
    comment = Column(sqlalchemy.types.Unicode(200))
    datetime = Column(sqlalchemy.types.DateTime(), nullable=False,  default=datetime.datetime.now())
    number=Column(sqlalchemy.types.Integer(),  nullable=False)

    
    def __init__(self):
        super(CustomerNumber,  self).__init__()
        
        preferences = Preferences.get_preferrences()
        numbers  = CustomerNumber.query.all()
        numbernumbers = [n.number for n in numbers]

        numbernumbers.append(preferences.first_customer_number-1) 
        number = max(numbernumbers)+1

        logger.debug( "new customer number is ", number)
        self.number = number

        
    def __unicode__(self):
        return str(self.number) or 'unknown customer number'
        
    
    class Admin(EntityAdmin):
        
        
        def is_editable(customer_number):
            if customer_number.number==None:
                return True
            else:
                return False
        
        verbose_name= _('Customer number')
        list_display = ['number','customers', 'comment' , 'datetime']
        form_display = list_display
        field_attributes = dict(
                                datetime=dict(name='created', editable=is_editable), 
                                number=dict(editable=is_editable), 
                                )

class Address(Entity):
    __tablename__='address'
    
    additional_address_info = Column(sqlalchemy.types.Unicode(100))
    street_box = Column(sqlalchemy.types.Unicode(100))
    city =Column(sqlalchemy.types.Unicode(100))
    region =Column(sqlalchemy.types.Unicode(100))
    zip = Column(sqlalchemy.types.Unicode(20))
    country = Column(sqlalchemy.types.Unicode(100))
    
    type=ManyToOne('ContactMechanismType')
    customer = ManyToOne('Customer')
    
    comment=Column(sqlalchemy.types.Unicode(100))
    
    is_shipping_address=Column(sqlalchemy.types.Boolean())
    is_billing_address=Column(sqlalchemy.types.Boolean())
    is_prefered_address=Column(sqlalchemy.types.Boolean())

#FIXME: (global)Refresh causes more than one address made to be shipping, preferred or billing if changed before hitting the refresh button
#seems Refresh reads the values from the db and 'adds' them to the changed ones in the object

#radiobuttonlike swap states property for shipping address
    def _set_is_shipping_address(self, b):
        customer_addresses = self.customer.addresses
        for a in customer_addresses:
            if a != self:
                a.is_shipping_address = False
        self.is_shipping_address = b
            
    def _get_is_shipping_address(self):
        return self.is_shipping_address
        
    _is_shipping_address = property(_get_is_shipping_address,  _set_is_shipping_address)
    
#radiobuttonlike swap states property for billing address
    def _set_is_billing_address(self, b):
        customer_addresses = self.customer.addresses
        for a in customer_addresses:
            if a != self:
                a.is_billing_address = False
        self.is_billing_address = b
            
    def _get_is_billing_address(self):
        return self.is_billing_address
        
    _is_billing_address = property(_get_is_billing_address,  _set_is_billing_address)
    
#radiobuttonlike swap states property for prefered address
    def _set_is_prefered_address(self, b):
        customer_addresses = self.customer.addresses
        for a in customer_addresses:
            if a != self:
                a.is_prefered_address = False
        self.is_prefered_address = b

            
    def _get_is_prefered_address(self):
        return self.is_prefered_address
        
    _is_prefered_address = property(_get_is_prefered_address,  _set_is_prefered_address)
    
    

    
    def __unicode__(self):
        return u'%s, %s %s, %s ' %(self.street_box,  self.zip,  self.city,  self.country) or u'unknown contact mechanism'
        
    class Admin(EntityAdmin):
        
        def get_depending_objects( self, address ):
            '''inform the customer that its addresses have changed'''
            address.customer.expire( ['addresses'] )
            return[ address.customer]
            #return address.customer.addresses


# This approach would have worked if we just wanted to propagate changes inside this object,
#        def flush(self, obj): 
#            '''inform the voucher that its positions have changed'''
#            EntityAdmin.flush( self, obj ) 
#            obj.expire( [ 'is_shipping_address' ] ) 

        verbose_name = _('Address')
        verbose_plural_name = _('Addresses')
        list_display = ['type','additional_address_info', 'street_box', 'zip',  'city', 'country' ,'_is_prefered_address','_is_billing_address','_is_shipping_address']
        form_display = ['type','comment','additional_address_info', 'street_box', 'zip', 'city', 'region',  'country' ,'_is_prefered_address','_is_billing_address','_is_shipping_address']
        field_attributes=dict(
                              _is_shipping_address = dict(delegate=delegates.BoolDelegate, name="Shipping",  editable=True), 
                              _is_billing_address = dict(delegate=delegates.BoolDelegate,  name="Billing", editable=True), 
                              _is_prefered_address = dict(delegate=delegates.BoolDelegate,  name="Prefered", editable=True)
                              )
        copy_exclude=['is_prefered_address', 'is_billing_address', 'is_shipping_address']


    
class ContactMechanism(Entity):
    __tablename__ = 'contact_mechanism'
    
    mechanism = Column(camelot.types.VirtualAddress(), nullable=False)
    type=ManyToOne('ContactMechanismType')
    customer = ManyToOne('Customer')
    is_prefered=Column(sqlalchemy.types.Boolean())
#    
##set is_prefered to false if mechanism is changed
#    def _set_mechanism(self, m):
#        self.mechanism = m
#        print "_set_mechnaism: ", m
##        self.is_prefered=False
#        
#    def _get_mechanism(self):
#        return self.mechanism
#        
#    _mechanism=property(_get_mechanism,  _set_mechanism)
    
#radiobuttonlike swap states property for prefered mechanisms of same mechnaism type
    def _set_is_prefered(self, b):
        same_mechanism_type_mechanisms = [m for m in self.customer.contact_mechanisms if m.mechanism[0] == self.mechanism[0]] #get all of same mechanism_type 'email', 'phone', etc.
        for m in same_mechanism_type_mechanisms:
            if b and m != self:
                m.is_prefered = False
        self.is_prefered = b

            
    def _get_is_prefered(self):
        return self.is_prefered
        
    _is_prefered = property(_get_is_prefered,  _set_is_prefered)
    
    
    def __unicode__(self):
        if self.type:
            return u"%s: %s (%s)" %(self.mechanism[0],  self.mechanism[1],  self.type) or u'unknown contact mechanism'
        else:
            return u"%s: %s" %(self.mechanism[0],  self.mechanism[1]) or u'unknown contact mechanism'
        
    class Admin(EntityAdmin):
        
        def get_depending_objects( self, mechanism ):
            '''inform the customer that its addresses have changed'''
#            mechanism.customer.expire( ['contact_mechanisms'] )
            mechanism.customer.expire( ['contact_mechanisms'] )
            return[mechanism.customer]
#            return mechanism.customer.contact_mechanisms
        
        verbose_name = _('Contact Mechanism')
        list_display = ['mechanism', '_is_prefered','type']
        form_display = list_display
        field_attributes=dict(
                              _mechanism = dict(delegate = delegates.VirtualAddressDelegate,  editable = True), 
                              _is_prefered = dict(delegate = delegates.BoolDelegate ,  editable = True) 
                              )

class ContactMechanismType(Entity):
    __tablename__ = 'contact_mechanism_type'
    type =Column(sqlalchemy.types.Unicode(30))
    def __unicode__(self):
        return self.type or u'unknown contact mechanism type'
        
    class Admin(EntityAdmin):
        verbose_name = 'Contact Mechanism Type'
        list_display = ['type']
        form_display = list_display

    
class Category(Entity):
    __tablename__ = 'category'
    
    name = Column(sqlalchemy.types.Unicode(50))
    customers = ManyToMany('Category',
                  tablename = 'categories',
                  local_colname = 'category.id',
                  remote_colname = 'customer.id' )
    
    def __unicode__(self):
        return str(self.id) or u'unknown category'
        
    class Admin(EntityAdmin):
        verbose_name = 'Category'
        list_display = ['name']
        form_display = list_display + []


class Industry(Entity):
    __tablename__ = 'industry'
    
    name = Column(sqlalchemy.types.Unicode(80))

    def __unicode__(self):
        return self.name or u'unknown industry'
        
    class Admin(EntityAdmin):
        verbose_name = 'Industry'
        list_display = ['name']
        form_display = ['name',  'customers']

#Voucher classes
class VoucherType(Entity):
    __tablename__ = 'voucher_type'
    
    name=Column(sqlalchemy.types.Unicode(30), nullable=False) #z.B 'Rechnung' as used in the title of Documents
    abbreviation=Column(sqlalchemy.types.Unicode(5), nullable=False) #z.B 'RE' for internal reference
    first_voucher_number=Column(sqlalchemy.types.Integer(),  default=1)
    successor_voucher_types = ManyToMany('VoucherType', tablename = 'successor_voucher_types', local_colname = 'voucher_type.id')# types to which this type of voucher can be converted Bsp. AG->AB->RE->GS...
    icon = Column(camelot.types.Image(upload_to='icons'))
    default_template = Column(sqlalchemy.types.Text())
    templates=OneToMany('PrintTemplate',  inverse='voucher_type')
    use_address = Column(camelot.types.Enumeration([(1,'billing address'), (2,'shipping address')] , index=True,  nullable = False))
    default_pre_text =Column(sqlalchemy.types.Text()) 
    default_post_text =Column(sqlalchemy.types.Text()) 

    def __unicode__(self):
        return self.name or u'unknown voucher type'
        
    class Admin(EntityAdmin):
        verbose_name = 'Voucher Type'
        list_display =  ['name', 'abbreviation', 'successor_voucher_types']
        form_display = ['icon','name', 'abbreviation',  'first_voucher_number', 'successor_voucher_types', 'use_address',  'templates',  'default_template', 'default_pre_text', 'default_post_text']
        field_attributes=dict(
                              default_template = dict(delegate= delegates.LocalFileDelegate), 
                              templates = dict(create_inline=True)
                              )
                              
class PrintTemplate(Entity):
    __tablename__='print_template'
    name = Column(sqlalchemy.types.Unicode(30), nullable=False)
    language = Column(camelot.types.Language(),   default=u'de_DE')
    template = Column(sqlalchemy.types.Text())
    voucher_type=ManyToOne(VoucherType)
    pre_text =Column(sqlalchemy.types.Text()) 
    post_text =Column(sqlalchemy.types.Text()) 
    
    def __unicode__(self):
        return self.name or u'unknown voucher type'
        
    class Admin(EntityAdmin):
        verbose_name = 'Voucher Type'
        list_display =  ['name', 'language', 'template']
        form_display =list_display+['pre_text', 'post_text']
        field_attributes=dict(
                              template = dict(delegate= delegates.LocalFileDelegate)
                              )
    
        
        
class VoucherPosition(Entity): #Belegposition
    __tablename__ = 'voucher_position'
    
    voucher = ManyToOne('Voucher')
    product = ManyToOne('Product',  backref='positions' ) 
    
    price = Column(sqlalchemy.types.Float(),  default=0,  nullable=False)
    incl_tax=Column(sqlalchemy.types.Boolean(),  default=False)
    product_number=Column(sqlalchemy.types.Unicode(100))
    product_name = Column(sqlalchemy.types.Unicode(500),  nullable = False)
    product_text = Column(sqlalchemy.types.Text())
    tax_key = ManyToOne('TaxKey',  required=True)
    amount = Column(sqlalchemy.types.Float(), default=1,  nullable=False)
    product_weight =Column(sqlalchemy.types.Float(), default=0)
    unit = ManyToOne('Unit')
    position_discount = Column(sqlalchemy.types.Float(), default=0)
    annulation_reason = Column(sqlalchemy.types.Text(),  default=None)
    
    def on_close(self):
        logger.info( "VoucherPositions closed")
        
    
    def _set_product(self, p):
        self.product = p
        #copy product data to position everytime product is changed
        self.copyFromProduct()
        
    def _get_product(self):
        return self.product
        
    _product=property(_get_product,  _set_product)

    @property
    def translated_unit_name(self):
        return self.unit.translated_name()
    
    @property
    def translated_unit_abbreviation(self):
        return self.unit.translated_abbreviation
        
    @property
    def translated_tax_key_name(self):
        return self.tax_key.translated_name(self.voucher.language)

    
    def copyFromProduct(self):
        """copy product data to position"""

        self.price = self.product.price
        self.incl_tax = self.product.incl_tax
        self.product_number = self.product.product_number
        self.product_name = self.product.product_name
        self.product_text = self.product.product_text_short
        self.tax_key = self.product.tax_key
        self.amount = 1
        self.product_weight = self.product.weight
        self.unit = self.product.unit
    
    @property
    def net_price(self):
        """always returns the netprice no matter if price is including VAT """
        if self.incl_tax:
            return round(self.price / ((self.tax_key.factor / 100) + 1), 2)
        else:
            return self.price
    
    @property
    def net_sum(self):
        """calculate the total price of this position excl. tax"""
        if not self.incl_tax: #no tax included, inore tax
            return round((self.amount * self.price) - (((self.amount * self.price) / 100 ) * self.position_discount), 2)
        elif self.tax_key: #tax included, tax key set, calculate and exclude tax
            return round(((self.amount * self.price) - (((self.amount * self.price) / 100 ) * self.position_discount))/((self.tax_key.factor / 100)+1), 2)
        else: #tax included, tax key not set, assume tax is zero
            return round((self.amount * self.price) - (((self.amount * self.price) / 100 ) * self.position_discount), 2)
    
    @property
    def position_tax(self):
        """calculate the tax amont for this position"""
        if self.tax_key:
            return round(self.net_sum /100 * self.tax_key.factor, 2)
        else:
            return 0
        
    @property
    def position_sum(self):
        """calculate the total sum of the position including tax"""
        return round(self.net_sum + self.position_tax, 2)

    @property
    def weight_sum(self):
        """calculate the total weight of the position"""
        return round(self.product_weight * self.amount, 2)
        
    @property
    def price_zero_warning(self):
        '''Check if the sum of this Position is zero and show a warning if so'''
        if self.position_sum ==0:
                return (u'Sum of this position is 0.00')

        
    def __unicode__(self):
        return u"position no "+unicode(self.id) or u'unknown payment term'
        
        
    class Admin(EntityAdmin):
        
        def currency_symbol(voucher_position):
            """returns the symbol for the vouchers currency"""
            if voucher_position.voucher.currency:
                return voucher_position.voucher.currency.symbol
            else:
                return u""
                
        def voucher_editable(voucher_position):
            '''retrun False if Voucher has been set to printed or canceled'''
            voucher = voucher_position.voucher
            if voucher.printed or voucher.canceled:
                return False
            else:
                return True


        def get_depending_objects( self, voucherpos ):
            '''inform the voucher that its positions have changed'''
            voucherpos.voucher.expire( ['voucher_positions'] )
            return[ voucherpos.voucher]

        
        verbose_name = 'Voucher Position'
        list_display = ['amount','unit','_product','product_name', 'price', 'incl_tax', 'tax_key', 'net_sum', 'position_tax','position_sum']
        form_display = ['_product','price_zero_warning','amount','unit','product_name', 'product_weight','price', 'incl_tax',   'tax_key', 'net_sum', 'position_tax','position_sum','weight_sum' ,'position_discount']
        field_attributes = dict( 
                                net_sum = dict( delegate = delegates.FloatDelegate,  suffix=currency_symbol),
                                position_tax = dict( delegate = delegates.FloatDelegate,  suffix=currency_symbol),
                                position_sum = dict( delegate = delegates.FloatDelegate,  suffix=currency_symbol), 
                                weight_sum= dict( delegate = delegates.FloatDelegate ), 
                                _product =  dict(delegate=delegates.Many2OneDelegate, target='Product',  editable=voucher_editable), 
                                position_discount = dict(suffix="%",  editable=voucher_editable), 
                                price_zero_warning = dict(delegate = delegates.NoteDelegate,  name='' , translate_content=True), 
                                amount=dict(editable=voucher_editable), 
                                unit=dict(editable=voucher_editable), 
                                product_name=dict(editable=voucher_editable), 
                                price=dict(editable=voucher_editable), 
                                incl_tax=dict(editable=voucher_editable), 
                                product_weight=dict(editable=voucher_editable), 
                                tax_key=dict(editable=voucher_editable)
                            )
        form_close_action=Refresh()
        form_actions=[CloseForm()]
        form_size=(700, 500)
    


class PaymentTerm(Entity):
    __tablename__ = 'payment_term'
    translations = OneToMany('PaymentTermTranslation',  inverse="payment_terms")
    name=Column(sqlalchemy.types.Unicode(50), nullable=False)
    text=Column(sqlalchemy.types.Text(), nullable=False)
    days=Column(sqlalchemy.types.Integer(), nullable=False)

    def translated_name(self,  language):
        """check if a translation for language is available and return the unicode string, otherwise return the classes default unicode string"""
        translation = [t for t in translations if t.language==language]
        if len(translation)>0:
            logger.info( "PaymentTerm.name translation found: ", translation.language,  translation.name )
            return translation.name
        else:
            logger.info( "No PaymentTerm.name translation found returning original")
            return self.name
    
    

    def translated_text(self,  language):
        """check if a translation for language is available and return the unicode string, otherwise return the classes default unicode string"""
        translation = [t for t in self.translations if t.language==language]
        if len(translation)>0:
            logger.info( "PaymentTerm.text translation found: ", translation[0].language,  translation[0].text )
            return translation[0].text
        else:
            logger.info( "No PaymentTerm.text translation found returning original")
            return self.text
    
    

    def __unicode__(self):
        return self.name or u'unknown payment term'
        
    class Admin(EntityAdmin):
        verbose_name = 'Payment Term'
        verbose_plural_name = 'Payment Terms'
        list_display = ['name', 'days']
        form_display = list_display+['text', 'translations']
        
class PaymentTermTranslation(Entity):
    payment_terms=ManyToOne('PaymentTerm')
    language = Column(camelot.types.Language(),   default=u'de_DE')
    text=Column(sqlalchemy.types.Text(), nullable=False)
    name = Column(sqlalchemy.types.Unicode(50), nullable=False)
    
    def __unicode__(self):
        return self.name or 'unknown payment term translation'
    
    class Admin(EntityAdmin):
        verbose_name = 'Payment Term Translation'
        list_display = ['language','name']
        form_display = list_display+['text']

class BusinessTransaction(Entity):
    """Businesstransactions act as kind of paperclips for related vouchers"""
    __tablename__='business_transaction'
    
    def __init__(self):
        super(BusinessTransaction,  self).__init__()
    
    def __unicode__(self):
        return unicode(self.id) or u'unknown business_transaction'
        
    class Admin(EntityAdmin):
        verbose_name = 'BusinessTransaction'
        verbose_plural_name = 'BusinessTransactions'
        list_display = ['id','vouchers']
        form_display = list_display
        field_attributes=dict(
                              id=dict(editable=False), 
                              vouchers=dict(editable=False), 
                              )
        form_close_action = CloseFormAction()

class ShippingMethod(Entity):
    """Businesstransactions act as kind of paperclips for related vouchers"""
    __tablename__='shipping_method'
    
    name = Column(sqlalchemy.types.Unicode(50), nullable=False)
    service=Column(sqlalchemy.types.Unicode(50), nullable=False)
    
    @property
    def label(self):
        return self.service+u' '+self.name
    
    def __init__(self):
        super(ShippingMethod,  self).__init__()
    
    def __unicode__(self):
        return self.label or u'unknown shipping method'
        
    class Admin(EntityAdmin):
        verbose_name = 'ShippingMethod'
        list_display = ['label']
        form_display = ['service',  'name']




class Voucher(Entity):#Beleg
#TODO: delete action so implementieren dass nut der letzte Voucher eines Types gelöscht werden kann wenn er noch nicht gedruckt wurde
#FIXME: copying of voucher causes error because of missing voucher number. we need to redefine copy() and  include a function to re-set the voucher type, there
    __tablename__ = 'voucher'

    voucher_type = ManyToOne('VoucherType', required = True)
    language = Column(camelot.types.Language(),   default=u'de_DE')
    voucher_positions = OneToMany('VoucherPosition',  backref='voucher', cascade='all, delete, delete-orphan',  single_parent=True)
    customer = ManyToOne('Customer', backref='vouchers', required = False)
    
    user = ManyToOne('User',  required = True,  backref='vouchers')
    transaction = ManyToOne("BusinessTransaction",  backref='vouchers')
    shipping_method = ManyToOne("ShippingMethod")
    
    voucher_number = Column(sqlalchemy.types.Integer(),  nullable=False)
    voucher_date = Column(sqlalchemy.types.Date(),  default=datetime.date.today())
    delivery_date  = Column(sqlalchemy.types.Date(),  default=datetime.date.today())
    due_date  = Column(sqlalchemy.types.Date())
    paid_date=Column(sqlalchemy.types.Date())
    
    canceled=Column(sqlalchemy.types.Boolean(),  default=False)
    printed=Column(sqlalchemy.types.Boolean(),  default=False)

    customer_number = Column(sqlalchemy.types.Unicode(200))
    customer_reference = Column(sqlalchemy.types.Text())
    company_name = Column(sqlalchemy.types.Unicode(200))
    company_additional = Column(sqlalchemy.types.Unicode(200))
    
    person_salutation = Column(sqlalchemy.types.Unicode(100))
    person_letter_salutation = Column(sqlalchemy.types.Unicode(150))
    person_firstname = Column(sqlalchemy.types.Unicode(100))
    person_lastname = Column(sqlalchemy.types.Unicode(100))
    
    additional_address_info = Column(sqlalchemy.types.Unicode(200))
    street_box = Column(sqlalchemy.types.Unicode(200))
    address_zip = Column(sqlalchemy.types.Unicode(20))
    address_city = Column(sqlalchemy.types.Unicode(100))
    address_country = Column(sqlalchemy.types.Unicode(100))
    
    vat_id = Column(sqlalchemy.types.Unicode(100))
    supplyer_id = Column(sqlalchemy.types.Unicode(100))
    
    payment_term=ManyToOne('PaymentTerm')
    
    currency = ManyToOne('Currency',  required=True)
    
    payment_term_text = Column(sqlalchemy.types.Text())
    payment_term_days = Column(sqlalchemy.types.Integer())
    #payment_term_date -> implemented as due_date
   
    pre_text=Column(sqlalchemy.types.Text()) 
    post_text=Column(sqlalchemy.types.Text())
    
    def __init__(self,  transaction=None):
        super(Voucher, self).__init__()
        #set default relationship values on creation
        default_currency = Preferences.get_preferrences().default_currency
        self.currency = default_currency
                
        myuser = User.query.filter_by(username=getpass.getuser()).first()
        if myuser:
            self.user=myuser
            
    def on_close(self):
        """This method is called by our customized lemonade.actions.CloseFormAction()"""
        pass
        
    def print_template(self):
        """retruns the path of the template selected for the vouchers language and type if availabel, otherwise use standard template"""
        translated_template = [template for template in self.voucher_type.templates if template.language ==self.language]
        if len(translated_template) > 0:
            templatepath = translated_template[0].template
            logging.info( 'found template for language ',  self.language,  ": ", templatepath)
        else:
            templatepath = self.voucher_type.default_template
            logging.info( 'No template for language ',  self.language, "using default: ", templatepath)
        return templatepath
        
    def get_voucher_type_name(self):
        """returns the translated voucher type"""
        translated_template = [template for template in self.voucher_type.templates if template.language ==self.language]
        if len(translated_template) > 0:
            voucher_type_name = translated_template[0].name 
            logging.info( 'found voucher_type_name for language ',  self.language,  ": ", voucher_type_name)
        else:
            voucher_type_name = self.voucher_type.name
            logging.info( 'No voucher_type_name for language ',  self.language, "using default: ", voucher_type_name)
        return voucher_type_name
        
        
    def get_pre_text(self):
        """retruns the pre_text of the template selected for the vouchers language and type if available, otherwise use default_pre_text"""
        translated_template = [template for template in self.voucher_type.templates if template.language ==self.language]
        if len(translated_template) > 0:
            pre_text = translated_template[0].pre_text
            logging.info( 'found pre_text for language ',  self.language,  ": ", pre_text)
        else:
            pre_text = self.voucher_type.default_pre_text
            logging.info( 'No pre_text for language ',  self.language, "using default: ", pre_text)
        return pre_text
        
    def get_post_text(self):
        """retruns the post_text of the template selected for the vouchers language and type if available, otherwise use default_pre_text"""
        translated_template = [template for template in self.voucher_type.templates if template.language ==self.language]
        if len(translated_template) > 0:
            post_text = translated_template[0].post_text
            logging.info( 'found post_text for language ',  self.language,  ": ", post_text)
        else:
            post_text = self.voucher_type.default_post_text
            logging.info( 'No post_text for language ',  self.language, "using default: ", post_text)
        return post_text
        
    @property
    def note(self):
        """display a note if this voucher is paid, not paid but duedate is in the past or has been canceled"""
        #FIXME: Translation does work but in Note Delegate the orginal String is shown
        if self.canceled:
            message = u'CANCELED'
            return message
        elif self.paid_date:
            message =  u'PAID'
            return message
        elif self.paid_date ==None  and self.due_date and self.due_date < datetime.date.today():
            message = u'OVERDUE' 
            message = ugettext(message)
            return message
        
    
    def _set_voucher_type(self, t):
        self.voucher_type=t
        
        if self.voucher_type:
            if self.voucher_number == None or self.voucher_number ==0 :
                vouchers_of_type = Voucher.query.filter_by(voucher_type = self.voucher_type)
                numbers = [voucher.voucher_number for voucher in vouchers_of_type]
                numbers.append(self.voucher_type.first_voucher_number-1) 
                number = max(numbers)+1
                logger.debug( "new number is ", number)
                self.voucher_number = number
                
        if self.customer:
            logger.debug("Copy customer data by _set_vouchertype")
            self.copyDataFromCustomer()
                
        if self.transaction==None:
            self.transaction=BusinessTransaction()
            logger.info( "transaction created")


    def _get_voucher_type(self):
        return self.voucher_type
    _voucher_type = property(_get_voucher_type,  _set_voucher_type)
    
    def _set_customer( self, c ):
        self.customer=c
        if self.voucher_type:
            self.copyDataFromCustomer()
        
    def _get_customer( self ):
        return self.customer
    
    _customer=property( _get_customer,  _set_customer )
    #hook for events
    def _set_payment_term(self,  t):
        self.payment_term=t
        if self.payment_term:
            self.payment_term_days=t.days
            self.due_date=self.voucher_date+relativedelta(days=t.days)
            text=t.translated_text(self.language)
            text=text.replace('#DUEDATE', self.due_date.strftime("%d.%m.%Y"))
            text=text.replace('#DAYS',str(self.payment_term_days))
            self.payment_term_text=text
        elif self.payment_term == None:
            self.payment_term_text=u""
            
            
        
    def _get_payment_term(self):
        return self.payment_term
        
    _payment_term=property(_get_payment_term, _set_payment_term)
    
    @property
    def payment_term_translated_name(self):
        return self.payment_term.translated_name(self.language)
        
    @property    
    def payment_term_translated_text(self):
        return self.payment_term.translated_text(self.language)
    
    @property
    def net_total(self):
        """calculates the total amount of all voucher positions excl. tax"""
        net_total=0
        for p in self.voucher_positions:
            net_total = net_total + p.net_sum
        return net_total
        
    @property
    def total_tax(self):
        """calculates the amount of tax on the voucher"""
        total=0
        for p in self.voucher_positions:
            total = total + p.position_tax
        return total
        
    @property
    def total_tax_values(self):
        ''''returns a dictionary with the sum of taxes for all the different taxrates on this voucher: taxvalues[tax_rate] '''
        taxvalues = dict()
        for p in self.voucher_positions:
            if taxvalues.has_key(p.tax_key.factor):
                taxvalues[p.tax_key.factor] = taxvalues[p.tax_key.factor] + p.position_tax
            else:
                taxvalues[p.tax_key.factor] = p.position_tax
        return taxvalues
        
    @property
    def total(self):
        """calculates the total amount of all voucher positions incl. tax"""        
        total=0
        for p in self.voucher_positions:
            total = total + p.position_sum
        return total
        
    @property
    def total_weight(self):
        """calculates the total weight of all goods onthe voucher"""
        total=0
        for p in self.voucher_positions:
            total = total + p.weight_sum
        return total
    
    def copyDataFromCustomer(self):
        """copy voucher relevant data from the customer to the voucher fields - this is usually triggered by _set_customer when the customer has changed"""
        if self.customer.customer_number:
            self.customer_number = self.customer.customer_number.number
            print self.customer.customer_number.number
            print self.customer.matchcode
        
        if self.customer.billing_address and self.voucher_type.use_address =='billing address':
            self.additional_address_info=self.customer.billing_address.additional_address_info
            self.street_box=self.customer.billing_address.street_box
            self.address_zip =  self.customer.billing_address.zip
            self.address_city = self.customer.billing_address.city
            self.address_region= self.customer.billing_address.region
            self.address_country = self.customer.billing_address.country
            print "using billing address"
            print self.customer.billing_address.street_box
            
            
        if self.customer.shipping_address and self.voucher_type.use_address =='shipping address':
            self.additional_address_info=self.customer.shipping_address.additional_address_info
            self.street_box=self.customer.shipping_address.street_box
            self.address_zip =  self.customer.shipping_address.zip
            self.address_city = self.customer.shipping_address.city
            self.address_region= self.customer.shipping_address.region
            self.address_country = self.customer.shipping_address.country
            print "using shipping address"
        
        self.language=self.customer.person_language
        self.company_name=self.customer.company_name
        self.company_additional=self.customer.company_additional
        self.person_salutation=self.customer.person_salutation
        self.person_letter_salutation=self.customer.person_letter_salutation
        self.person_lastname=self.customer.person_lastname
        self.person_firstname=self.customer.person_firstname
        self.vat_id = self.customer.vat_id
        self.supplyer_id = self.customer.supplyer_id
        self._payment_term=self.customer.payment_term #fields of payment_term will be copied by _payment_term property
        
        self.pre_text = self.get_pre_text()
        self.post_text = self.get_post_text()
    
    def __unicode__(self):
        return self.voucher_type.name +u' '+unicode(self.voucher_number) or u'unknown voucher'
    

    class Admin(EntityAdmin):
        validator = VoucherValidator 
        #functions for dynamic field properties
        def voucher_type_editable(voucher):
            '''returns true if voucher_type is None'''
            if voucher.voucher_type == None:
                return True
            else:
                return False
                
        def voucher_number_editable(voucher):
            """retruns true if voucher_number is None or 0"""
            if voucher.voucher_number == None or voucher.voucher_number ==0:
                return True
            else:
                return False
        
        def currency_symbol(voucher):
            """returns the symbol for the vouchers currency"""
            if voucher.currency:
                return voucher.currency.symbol
            else:
                return u""
                
        def voucher_editable(voucher):
            if voucher.printed or voucher.canceled:
                return False
            else:
                return True
            
        
        verbose_name = 'Voucher'
        verbose_plural_name = 'Vouchers'
        list_display = ['note','user','_customer','_voucher_type', 'voucher_number', 'voucher_date', 'total' , 'due_date']

        list_filter = ['voucher_type.name', 'voucher.customer.matchcode']
        delete_mode='on_confirm'
        field_attributes = dict( net_sum = dict( delegate = delegates.FloatDelegate),
                                 net_total = dict( delegate = delegates.FloatDelegate, suffix=currency_symbol),
                                 total_tax = dict( delegate = delegates.FloatDelegate, suffix=currency_symbol),
                                 total = dict( delegate = delegates.FloatDelegate,  suffix=currency_symbol), 
                                 total_weight=dict( delegates = delegates.FloatDelegate), 
                                 voucher_positions=dict(required=True, editable=voucher_editable), 
                                 _customer = dict(delegate=delegates.Many2OneDelegate, target=Customer, editable=voucher_editable), 
                                 _payment_term = dict(delegate=delegates.Many2OneDelegate, target=PaymentTerm, editable=voucher_editable), 
                                 user =dict(editable=voucher_editable),
                                 voucher_date =dict(editable=voucher_editable),
                                 delivery_date =dict(editable=voucher_editable),
                                 company_name =dict(editable=voucher_editable),
                                 person_salutation =dict(editable=voucher_editable),
                                 person_firstname =dict(name=_(u'firstname'), editable=voucher_editable),
                                 person_lastname =dict(name=_(u'lastname'), editable=voucher_editable),
                                 additional_address_info=dict(editable=voucher_editable),
                                 street_box =dict(editable=voucher_editable),
                                 address_zip =dict(editable=voucher_editable),
                                 address_city =dict(editable=voucher_editable),
                                 address_country =dict(editable=voucher_editable),
                                 currency =dict(editable=voucher_editable),
                                 payment_term_text =dict(editable=voucher_editable),
                                 due_date =dict(editable=voucher_editable),
                                 vat_id=dict(editable=voucher_editable),
                                 language=dict(editable=voucher_editable),
                                 company_additional=dict(editable=voucher_editable),
                                 pre_text=dict(editable=voucher_editable),
                                 post_text=dict(editable=voucher_editable),
                                 shipping_method=dict(delegate = delegates.ManyToOneChoicesDelegate,  editable=voucher_editable),
                                 #these are only editable when voucher_number or voucher_type are None or zero
                                 _voucher_type= dict(delegate=delegates.Many2OneDelegate, target=VoucherType,  editable=voucher_type_editable), 
                                  voucher_number =dict(editable=voucher_number_editable), 
                                 #these are never editable
                                 customer_number =dict(editable=False), 
                                 note = dict(delegate = delegates.NoteDelegate,  name='Note' ), 
                                 transaction = dict(editable=False,  related_toolbar_actions=[None])
                    )
                    
                    
        form_display = [forms.HBoxForm([['user'], ['note']]),  
                        forms.HBoxForm([
                                       [ 
                                       #left top box
                                       forms.VBoxForm([['_voucher_type', 'voucher_number','customer_number',  'voucher_date', 'delivery_date', 'paid_date','transaction', 'printed',  'canceled' , 'vat_id',  forms.Stretch()]])
                                       ], 
                                       #right top box
                                       [
                                       forms.VBoxForm([['_customer', 'company_name', 'company_additional', 'person_firstname', 'person_lastname', 'additional_address_info',  'street_box', 'address_zip', 'address_city', 'address_country',  forms.Stretch()]])
                                       ]
                                       ]), 
                                       'pre_text', 
                        forms.VBoxForm([
                                        ['voucher_positions']
                                        ]), 
                        forms.HBoxForm([['language', 'currency', 'total_weight', 'shipping_method'], ['_payment_term', 'due_date','net_total', 'total_tax', 'total']]), 
                        'payment_term_text', 
                        'post_text'
                        ]
        form_size=(1000, 800)
        copy_deep={'voucher_positions':{}}
        copy_exclude=['voucher_number','due_date', 'paid_date','delivery_date','payment_term_text','canceled', 'printed' , 'transaction']
        form_actions=[ConvertVoucher(),  PrintVoucher()] #CheckVatID() #SetTaxNull() # SendVochuerByEmail() -> ask for creating Contact entry
        list_actions=[ConvertVoucher()]
        form_close_action = ValidateFormBeforeCloseFormAction()
        #get_form_actions
        save_mode="on_change"


#Product Management Classes
class Product(Entity):
    __tablename__ = 'product'
    #TODO: und Formulare / Listen stylen
    
    product_number =  Column(sqlalchemy.types.Integer(), nullable=False)
    
    product_type = ManyToOne('ProductType')
    
    product_name = Column(sqlalchemy.types.Unicode(500))
    product_text_short = Column(sqlalchemy.types.Text())
    product_text_long = Column(sqlalchemy.types.Text())
    
    stock = Column(sqlalchemy.types.Float(),  nullable=False,  default=0)
    min_stock = Column(sqlalchemy.types.Float(),  nullable=False,  default=0)
    
    price = Column(sqlalchemy.types.Float(), nullable=False,  default=0)
    incl_tax = Column(sqlalchemy.types.Boolean,  default=False)
    
    currency = ManyToOne('Currency')
    
    tax_key = ManyToOne('TaxKey',  required=True)
    unit = ManyToOne('Unit')
    
    hazmat =Column(sqlalchemy.types.Boolean(),  default=False)
    weight =Column(sqlalchemy.types.Float(),  nullable=False,  default=0)
    product_picture = Column(camelot.types.Image())

    document  = ManyToOne('Document')

    @property
    def net_price(self):
        if self.incl_tax and self.tax_key:
            return round(self.price / ((self.tax_key.factor / 100)+1), 2)
        else:
            return self.price
    
    @property
    def brut_price(self):
        if self.incl_tax:
            return self.price
        elif self.tax_key:
            return round(self.price * ((self.tax_key.factor / 100)+1), 2)
        else:
            return None
    
    
    def __unicode__(self):
        return self.product_name or u'unknown product'
        
    class Admin(EntityAdmin):
        def currency_symbol(voucher):
            """returns the symbol for the vouchers currency"""
            if voucher.currency:
                return voucher.currency.symbol
            else:
                return u""
        
        verbose_name = 'Product'
        list_display = ['product_number', 'product_type',  'product_name', 'net_price', 'tax_key', 'brut_price' ,  'currency']
        form_display = ['product_number', 'product_type', 'product_name',  'product_text_short', 'product_text_long', 'product_picture','unit',  'min_stock', 'stock', 'price', 'incl_tax',  'tax_key', 'net_price','brut_price','weight','hazmat', 'document'  ]
        field_attributes = dict(
                                net_price  = dict( suffix=currency_symbol), 
                                brut_price  = dict( suffix=currency_symbol), 
                                price  = dict( suffix=currency_symbol), 
                                currency  = dict(requred=True)
                                )
    
class TaxKey(Entity):#steuerschlüssel
    __tablename__ = 'tax_key'
    name=Column(sqlalchemy.types.Unicode(30), nullable=False) #name is default language
    factor =Column(sqlalchemy.types.Float(), nullable=False)
    abbreviation=Column(sqlalchemy.types.Unicode(30), nullable=False)
    translations = OneToMany('TaxKeyTranslation',  inverse="tax_key")
    
    def translated_name(self,  language):
        """check if a translation for language is available and return the unicode string, otherwise return the classes default unicode string"""
        translation = [t for t in self.translations if t.language==language]
        if len(translation)>0:
            logger.info( "TaxKey translation found: ", translation[0].language,  translation[0].name )
            return translation[0].name
        else:
            logger.info( "No TaxKey translation found returning original")
            return self.name


    def __unicode__(self):
        return self.abbreviation or u'unknown tax key'
        
    class Admin(EntityAdmin):
        verbose_name = 'Tax Key'
        list_display = ['abbreviation','name', 'factor']
        form_display = list_display + ['translations']
        
class TaxKeyTranslation(Entity):
    tax_key=ManyToOne('TaxKey')
    language = Column(camelot.types.Language(),   default=u'de_DE')
    name = Column(sqlalchemy.types.Unicode(30), nullable=False)
    
    class Admin(EntityAdmin):
        verbose_name = 'Tax Key Translation'
        list_display = ['language','name']
        form_display = list_display#+['tax_keys']


class Unit(Entity):
    __tablename__ = 'unit'
    
    name = Column(sqlalchemy.types.Unicode(30), nullable=False)#name is default language
    abbreviation = Column(sqlalchemy.types.Unicode(15), nullable=False)
    translations = OneToMany('UnitTranslation',  inverse="units")
    
    def translated_name(self,  language):
        """check if a translation for language is available and return the unicode string, otherwise return the classes default unicode string"""
        translation = [t for t in self.translations if t.language==language]
        if len(translation)>0:
            logger.info( "Unit.name translation found: ", translation[0].language,  translation[0].name )
            return translation[0].name
        else:
            logger.info( "No.name Unit translation found returning original")
            return self.name
    
    def translated_abbreviation(self,  language):
        """check if a translation for language is available and return the unicode string, otherwise return the classes default unicode string"""
        translation = [t for t in self.translations if t.language==language]
        if len(translation)>0:
            logger.info( "Unit.abbreviation translation found: ", translation[0].abbreviation,  translation[0].name )
            return translation[0].abbreviation
        else:
            logger.info( "No Unit.abbreviation translation found returning original")
            return self.abbreviation
    
    def __unicode__(self):
        return self.name or u'unknown unit'
        
    class Admin(EntityAdmin):
        verbose_name = 'Unit'
        list_display = ['name', 'abbreviation']
        form_display =list_display+['translations']
        
        
class UnitTranslation(Entity):
    units=ManyToOne('Unit')
    language = Column(camelot.types.Language(),   default=u'de_DE')
    name=Column(sqlalchemy.types.Unicode(30), nullable=False)
    abbreviation =Column(sqlalchemy.types.Unicode(15), nullable=False)
    
    def __unicode__(self):
        return self.name
    
    class Admin(EntityAdmin):
        verbose_name = 'Unit Translation'
        list_display = ['language','name', 'abbreviation']
        form_display =list_display#+['units']

    
class ProductType(Entity):
    __tablename__ = 'product_type'
    
    name=Column(sqlalchemy.types.Unicode(15), nullable=False)
    abbreviation =Column(sqlalchemy.types.Unicode(15), nullable=False)
    
    def __unicode__(self):
        return self.name or u'unknown product type'
        
    class Admin(EntityAdmin):
        verbose_name = 'Product Type'
        list_display = ['name', 'abbreviation']
        form_display =list_display
    
#Contact management classes
class Contact(Entity):
    __tablename__ = 'contact'
    using_options(order_by='-datetime')
    customer = ManyToOne('Customer', backref='contacts')
    summary = Column(sqlalchemy.types.Text(), nullable=False)
    text = Column(camelot.types.RichText())
    type = Column(camelot.types.Enumeration([(1,None), (2,'email'), (3,'call'), (4,'letter'), (5,'fax'), (6,'social media message'), (7,'note')]), nullable=False)
    direction = Column(camelot.types.Enumeration([(1,None), (2,'out'), (3,'in'), (4,'internal')]),  nullable=False)
    file = Column(camelot.types.File())
    datetime = Column(sqlalchemy.types.DateTime(), nullable=False,  default=datetime.datetime.now())
    duration = Column(sqlalchemy.types.Time())
    
    opportunity = ManyToOne('Opportunity',  backref='contacts')
    user = ManyToOne('User',  backref='contacts')
    
    outcome = Column(camelot.types.Enumeration([(1,None), (2,'not reached'), (3,'success'), (4,'will call back'), (5,'absent')]))
    
    def __init__(self):
        super(Contact,  self).__init__()
        myuser = User.query.filter_by(username=getpass.getuser()).first()
        if myuser:
            self.user=myuser
        
    
    def __unicode__(self):
        return self.summary or u'unknown contact'
        
    class Admin(EntityAdmin):
        
        verbose_name = 'Contact'
        list_display = ['user', 'customer','datetime', 'type', 'direction',  'duration',  'summary', 'opportunity', 'file']
        form_display = list_display + ['text']
        form_actions = [CallPreferedPhone(), SendEmail(), SendStandardEmail()]
        list_filter = ['user.username', 'type','direction','opportunity.short_description']
        drop_action=ContactFromEmail()
    

#usermanagement
class User(Entity):
    __tablename__ = 'user'
    
    username=Column(sqlalchemy.types.Unicode(30),  nullable=False)
    firstname=Column(sqlalchemy.types.Unicode(100),  nullable=False)
    lastname=Column(sqlalchemy.types.Unicode(100),  nullable=False)
    
    def __unicode__(self):
        return self.username or u'unknown contact_type'
        
    class Admin(EntityAdmin):
        verbose_name = 'User'
        list_display = ['username', 'firstname', 'lastname']
        form_display = list_display+[]   


#document management classes    
class Document(Entity):
    __tablename__ = 'document'
    
    customer = ManyToOne('Customer', backref='documents')
    document_name = Column(sqlalchemy.types.Unicode(100),  nullable=False)
    description = Column(sqlalchemy.types.Text())

    type = ManyToOne('DocumentType',  backref='documents',  required=True)
    file = Column(camelot.types.File( upload_to = 'documents'))
    date = Column(sqlalchemy.types.Date(), default = datetime.date.today())
    archive_expiration_date = Column(sqlalchemy.types.Date(), default =datetime.date.today()+relativedelta(years=10)) #default for now + 10 years
    
    user = ManyToOne('User')
    tags= ManyToMany('Tag', tablename = 'document_tags', local_colname = 'document.id', remote_colname = 'tag.id' )
    
    def __init__(self):
        super(Document, self).__init__()
        myuser = User.query.filter_by(username=getpass.getuser()).first()
        if myuser:
            self.user=myuser
    
    def __unicode__(self):
        return self.document_name or u'unknown document'
        
    class Admin(EntityAdmin):
        verbose_name = 'Document'
        list_display = ['document_name','date','type','customer', 'file','description', 'user']
        form_display = list_display+['archive_expiration_date', 'tags']
        form_actions=[SendDocumentByEmail()]
        list_actions = [SendDocumentByEmail()]
        
class Tag(Entity):
    __tablename__="tag"
    name = Column(sqlalchemy.types.Unicode(100))
    documents = ManyToMany('Document', tablename = 'document_tags', local_colname = 'tag.id', remote_colname = 'document.id' )
    customers = ManyToMany('Customer', tablename = 'customer_tags', local_colname = 'tag.id', remote_colname = 'customer.id' )
    comment = Column(sqlalchemy.types.Unicode(100))

    def __unicode__(self):
        return self.name or u'unknown contact_type'

    class Admin(EntityAdmin):
        verbose_name = 'Tag'
        list_display = ['name', 'comment']
        form_display = list_display+[
                                                              forms.TabForm( 
                                                                 [
                                                                 (_('Customers with tag'),  ['customers']),
                                                                 (_('Documents with tag'),  ['documents'])
                                                                 ]
                                                             )]


    
class DocumentType(Entity):
    __tablename__ = 'document_type'

    type=Column(sqlalchemy.types.Unicode(100),  nullable=False)
    description=Column(sqlalchemy.types.Text())
    
    def __unicode__(self):
        return self.type or u'unknown Document Type'
        
    class Admin(EntityAdmin):
        verbose_name = 'Document Type'
        list_display = ['type',  'description']
        form_display = list_display + []
        
#Sales Management Classes
class Opportunity(Entity):
    __tablename__ = 'opportunity'

    customer = ManyToOne('Customer', backref='opportunities',  required=True)
    
    short_description = Column(sqlalchemy.types.Text(),  nullable=False)
    long_description = Column(camelot.types.RichText())
    leadsource = ManyToOne('Leadsource', required=True)
    opening_date = Column(sqlalchemy.types.Date(), nullable=False,  default=datetime.date.today())
    expected_closing_date = Column(sqlalchemy.types.Date(), nullable=False, default=datetime.date.today())
    value = Column(sqlalchemy.types.Float())
    currency = ManyToOne('Currency', required=True)
    probabilitie= Column(camelot.types.Rating())
   
    user = ManyToOne('User', required=True)
    status = ManyToOne('OppStatus', required=True)

    sales_stage = ManyToOne("SalesStage",  backref="opportunities", required=True)
    
    next_step_date=Column(sqlalchemy.types.Date(), nullable=False, default=datetime.date.today())
    next_step_description = Column(sqlalchemy.types.Text())
    next_step_action = ManyToOne('NextStepAction',  backref = 'opportunities')
    
    rejection_reason = ManyToOne('OppRejectionReason',  backref='opportunities')
    satisfaction_rating = Column(camelot.types.Rating())
    
    def __init__(self):
        super(Opportunity, self).__init__()
        
        #set default relationship values on creation   
        #set user to currently logged in user

        myuser = User.query.filter_by(username=getpass.getuser()).first()
        if myuser:
            self.user=myuser
            
        #set status to open
        mystatus = OppStatus.query.filter_by(name='open').first()
        if mystatus:
            self.status=mystatus
            
        #set currency to Preferences.currency
        default_currency = Preferences.get_preferrences().default_currency
        self.currency = default_currency
        

    
    def __unicode__(self):
        return self.short_description or u'unknown opportunity'
        
    class Admin(EntityAdmin):
        verbose_name = 'Opportunity'
        list_display = ['user','customer','short_description','status' , 'opening_date',  'expected_closing_date', 'value', 'currency',  'probabilitie',  'sales_stage', 'leadsource']
        form_display = list_display + ['long_description','contacts', 'next_step_date','next_step_action',  'next_step_description' , 'satisfaction_rating','rejection_reason']
        form_actions=[CallPreferedPhone(), SendEmail(), SendStandardEmail()]
        form_size=(1200, 800)
        list_filter = ['status.name','user.username', 'sales_stage.stage','next_step_action.action']

class SalesStage(Entity):
    __tablename__ = 'sales_stage'
    stage = Column(sqlalchemy.types.Unicode(100), nullable=False)
    
    def __unicode__(self):
        return self.stage or u'unknown sales stage'
        
    class Admin(EntityAdmin):
        verbose_name = 'SalesStage'
        list_display = ['stage']
        form_display = ['stage']
        
class NextStepAction(Entity):
    __tablename__ = 'next_step_action'

    action = Column(sqlalchemy.types.Unicode(100), nullable=False)
    
    def __unicode__(self):
        return self.action or u'unknown next step'
        
    class Admin(EntityAdmin):
        verbose_name = 'NextStepAction'
        list_display = ['action']
        form_display = ['action']
        
        

class Preferences(Entity):
    """Entity to hold company details and application settings. This table usually has only one row and the settings dialog is opened through lemonade.actions.OpenSettingsView"""
    __tablename__ = 'preferences'
    
    #company preferences
    logo=Column(camelot.types.Image(upload_to = u'logo'))
    company_name = Column(sqlalchemy.types.Unicode(100))
    company_additional =  Column(sqlalchemy.types.Unicode(100))
    company_address1 = Column(sqlalchemy.types.Unicode(100))
    company_address2 = Column(sqlalchemy.types.Unicode(100))
    company_zip =  Column(sqlalchemy.types.Unicode(100)) 
    company_city =  Column(sqlalchemy.types.Unicode(100)) 
    company_country =  Column(sqlalchemy.types.Unicode(100)) 
    company_email =  Column(sqlalchemy.types.Unicode(100))
    company_website = Column(sqlalchemy.types.Unicode(100))
    company_vat_id = Column(sqlalchemy.types.Unicode(100)) 
    company_bank = Column(sqlalchemy.types.Unicode(100))
    company_BIC =  Column(sqlalchemy.types.Unicode(100))
    company_IBAN = Column(sqlalchemy.types.Unicode(100))
    company_legal_form = Column(sqlalchemy.types.Unicode(100))
    company_phone = Column(sqlalchemy.types.Unicode(100)) 
    company_fax =  Column(sqlalchemy.types.Unicode(100))
    company_legal_info1= Column(sqlalchemy.types.Unicode(100))
    company_legal_info2= Column(sqlalchemy.types.Unicode(100))
    company_legal_info3= Column(sqlalchemy.types.Unicode(100))
    
    #imap server preferences
    imap_server = Column(sqlalchemy.types.Unicode(100))
    imap_user = Column(sqlalchemy.types.Unicode(100))
    imap_password = Column(sqlalchemy.types.Unicode(100))
    imap_use_tls =  Column(sqlalchemy.types.Boolean(),  default=True)
    imap_fetch_frequency = Column(sqlalchemy.types.Integer(),  default=10)
    
    #telephony preferences
    telephony_system = Column(camelot.types.Enumeration([(1,'Command Line Interface'), (2,'Lemonade Android Connector')]), nullable=False)
    #external command line dialer
    commandline="Command Line Interface"
    telephony_system_call_string = Column(sqlalchemy.types.Unicode(100))
    telephony_default_user = Column(sqlalchemy.types.Unicode(100))
    telephony_default_password = Column(sqlalchemy.types.Unicode(100))
    
    #Lemonade android connector
    android = u"Lemonade Android Connector"
    android_device_ip = Column(sqlalchemy.types.Unicode(100))
    android_device_port = Column(sqlalchemy.types.Integer())
    android_device_timeout = Column(sqlalchemy.types.Integer(),  nullable=False,  default=1)
    android_device_secret = Column(sqlalchemy.types.Unicode(100))
    android_use_call = Column(sqlalchemy.types.Boolean(),  nullable=False,  default=True)
    
    #other preferences (currency and printing)
    default_currency = ManyToOne('Currency')
    default_language = Column(camelot.types.Language(),  default=u'de_DE')
    first_customer_number = Column(sqlalchemy.types.Integer(), default=1,  nullable=False)
    
    printer_margin_left = Column(sqlalchemy.types.Integer(),  default=24)
    printer_margin_top = Column(sqlalchemy.types.Integer(),  default=15)
    printer_margin_right = Column(sqlalchemy.types.Integer(),  default=24)
    printer_margin_bottom = Column(sqlalchemy.types.Integer(),  default=5)
    printer_margin_unit = Column(camelot.types.Enumeration([(0,'Millimeter'), (2,'Inch')]),  index = True, nullable=False)
    printer_page_size = Column(camelot.types.Enumeration([(4,'A4'),(2,'Letter'), (0,'A0'), (1,'A1'), (2,'A2'), (3,'A3'), (5,'A5'), (6,'A6'), (7,'A7'), (8,'A8'), (9,'A9')]),  index = True, nullable=False)
    printer_page_orientation = Column(camelot.types.Enumeration([(0,'Portrait'), (1,'Landscape')]),  index = True, nullable=False)

    
    def __unicode__(self):
        return self.company_name or u'unknown preferences'
    
    @classmethod
    def get_preferrences(self):
        """retruns the Preferences Object that holds the preferences data"""
        preferences = self.query.filter_by(id=1).first()
        if preferences == None:
            preferences = Preferences()
        return preferences
        
    class Admin(EntityAdmin):
        verbose_name = 'Preferences'
        list_display = ['company_name']
        form_display = [
                        forms.TabForm( 
                                      [
                                        (_('Company'),['logo','company_name', 'company_additional', 'company_legal_form', 'company_address1',  'company_address2' , 'company_zip', 'company_city', 'company_country', 'company_legal_info1', 'company_legal_info2', 'company_legal_info3',    forms.Stretch()]), 
                                        (_('Contact'),['default_language','company_phone', 'company_fax', 'company_email', 'company_website' , forms.Stretch()]), 
                                        (_('Financial'),['company_bank', 'company_IBAN', 'company_BIC', 'company_vat_id', 'default_currency',  forms.Stretch()]), 
                                        (_('IMAP Server'),['imap_server', 'imap_user', 'imap_password', 'imap_use_tls', 'imap_fetch_frequency',    forms.Stretch()]), 
                                        (_('Telephony'),['telephony_system','commandline','telephony_system_call_string', 'telephony_default_user', 'telephony_default_password','android','android_device_ip','android_device_port', 'android_device_timeout', 'android_device_secret', 'android_use_call',  forms.Stretch()]), 
                                        (_('Printing'),['printer_margin_left', 'printer_margin_top', 'printer_margin_right', 'printer_margin_bottom', 'printer_margin_unit', 'printer_page_size', 'printer_page_orientation', forms.Stretch()]), 
                                        ]
                                    )
                                ]
        field_attributes = dict(
                                commandline=dict(delegate =delegates.LabelDelegate,  translate_content=True), 
                                android=dict( delegate = delegates.LabelDelegate,  translate_content=True)
                                )


class Currency(Entity):
    __tablename__ = 'currency'
    
    name = Column(sqlalchemy.types.Unicode(30),  nullable=False)
    abbreviation = Column(sqlalchemy.types.Unicode(5),  nullable=False)
    symbol = Column(sqlalchemy.types.Unicode(5),  nullable=False)
    
    def __unicode__(self):
        return self.name or u'unknown currency'
        
    class Admin(EntityAdmin):
        verbose_name = 'Currency'
        verbose_plural_name = 'Currencies'
        list_display = ['name', 'abbreviation', 'symbol']
        form_display = list_display

class Leadsource(Entity):
        __tablename__ = 'leadsource'
        name = Column(sqlalchemy.types.Unicode(30),  nullable=False)
        description=Column(sqlalchemy.types.Text())
        
        def __unicode__(self):
            return self.name or u'unknown lead source'
            
        class Admin(EntityAdmin):
            verbose_name = 'Lead Source'
            verbose_plural_name = 'Lead Sources'
            list_display = ['name', 'description']
            form_display = list_display
            
            

class OppStatus(Entity):
        __tablename__ = 'opp_status'
        name = Column(sqlalchemy.types.Unicode(30),  nullable=False)
        description=Column(sqlalchemy.types.Text())
        
        def __unicode__(self):
            return self.name or u'unknown currency'
            
        class Admin(EntityAdmin):
            verbose_name = 'Opportunity Status'
            verbose_plural_name = 'Opportunity Statuses'
            list_display = ['name', 'description']
            form_display = list_display


class OppRejectionReason(Entity):
        __tablename__ = 'opp_rejection_reason'
        name = Column(sqlalchemy.types.Unicode(30),  nullable=False)
        description=Column(sqlalchemy.types.Text())
        
        def __unicode__(self):
            return self.name or u'unknown currency'
            
        class Admin(EntityAdmin):
            verbose_name = 'Opportunity Rejection Reason'
            verbose_plural_name = 'Opportunity Rejection Reasons'
            list_display = ['name', 'description']
            form_display = list_display


class EmailTemplate(Entity):
    """Templates for email Actions"""
    __tablename__='email_template'    
    name=Column(sqlalchemy.types.Unicode(100),  nullable=False)
    subject=Column(sqlalchemy.types.Unicode(100),  nullable=False)
    body=Column(camelot.types.RichText(),  nullable=False)
    attachment_file=Column(camelot.types.File(upload_to='email_template_attachments'))
    language = Column(camelot.types.Language(),  default=u'de_DE')
    email_action = ManyToOne('EmailAction',  backref='email_templates',  use_alter=True)

    def __unicode__(self):
        return self.name or u'unknown email template'
        
    class Admin(EntityAdmin):
        verbose_name = 'Email Template'
        list_display = ['name', 'language','subject']
        form_display = list_display + ['body', 'attachment_file']
    

class EmailAction(Entity):
    """defining standard email actions"""
    __tablename__='email_action'
    label = Column(sqlalchemy.types.Unicode(100),  nullable=False)
    icon=Column(camelot.types.Image(upload_to='icons'))
    description = Column(sqlalchemy.types.Text(),  nullable=True)
    create_contact_entry = Column(sqlalchemy.types.Boolean(), default=True)
    contact_entry_summary = Column(sqlalchemy.types.Text(), default=u"")
    contact_entry_text = Column(camelot.types.RichText(),  default=u"")
#    default_language_template = OneOne('EmailTemplate', inverse='email_action')
    default_language_template = ManyToOne('EmailTemplate', use_alter=True)
    show_in_customer_context = Column(sqlalchemy.types.Boolean(),  default=True) #action is shown in the send email context dialog of customers if True

    def get_email_content(self,  customer):

        """get the template for the needed language if available and enabled, otherwise get default_language_template,
        render the template and return a dict(email, subject, body,attachmentfile),
        make contact entry if necessary"""
        assert type(customer) == Customer
        prefered_language = customer.person_language
        
        template = None 
        #see if we have a template here that maches the language
        t = [t for t in self.email_templates if t.language == prefered_language]
        if len(t) >=1:
            template = t[0]
        elif self.default_language_template:
            template = self.default_language_template
        
        if template:
            if customer.prefered_email:
                email=customer.prefered_email
            else:
                email=unicode(_(u'No email address...'))
            attachmentpath = None
            user_firstname = None
            user_lastname = None
            
            user = User.query.filter_by(username=getpass.getuser()).first()
            if user.firstname and user.lastname:
                user_firstname = user.firstname
                user_lastname = user.lastname
            
            if customer.prefered_email:
                email=customer.prefered_email.mechanism[1]
                
            if template.attachment_file:
                attachmentpath = os.path.abspath(os.path.join(settings.CAMELOT_MEDIA_ROOT(),'email_template_attachments',  template.attachment_file.name))
            
            context = {
                           'email_address' : email, 
                           'salutation' : customer.person_salutation, 
                           'letter_salutation' : customer.person_letter_salutation, 
                           'firstname' : customer.person_firstname, 
                           'initials' : customer.person_initials, 
                           'lastname' : customer.person_lastname, 
                           'company_name' : customer.company_name, 
                           'company_additional' : customer.company_additional, 
                           #TODO :  define other fields here...
                           'attachment_file_name'  :  template.attachment_file, 
                           'user_firstname' : user_firstname, 
                           'user_lastname' : user_lastname, 
                           }
            
            #remove html for email
            plaintext_body_lines = text_from_richtext(template.body)
            plaintext_body=u''
            #fixme: this loop seems to cause an empty line in the beginning of the email body
            for line in plaintext_body_lines:
                plaintext_body=plaintext_body+u'\n'+line
                
            e = Environment()
            template_body=e.from_string(plaintext_body) 
            template_subject=e.from_string(template.subject)
            
            body=template_body.render(context)
            subject=template_subject.render(context)
        
            contents = { 'email_address':email, 'body':body, 'subject':subject, 'attachment': attachmentpath}
            return contents
            
        else:
            return None
        

    def get_mailto_url(self, customer):
        """get the email content and form a valid mailto url"""
        url=None
        content = self.get_email_content(customer)
        if content:
            if content['attachment']:
                url = QUrl(u'mailto:%s?subject=%s&body=%s&Attachment=%s' %(content["email_address"],  content["subject"], content["body"],content["attachment"]))
            else:
                url = QUrl(u'mailto:%s?subject=%s&body=%s' %(content["email_address"], content["subject"], content["body"],))
            return url
        else:
            return u""
        
    def make_contact_entry(self, customer):
        '''creates a new Contact object entry for this email action and the given customer and returns a Contact object' (not stored)'''
        assert type(customer) == Customer

        contact = Contact()
        contact.summary =self.contact_entry_summary
        contact.text = self.contact_entry_text+'<br>--<br>'+self.get_email_content(customer)['email_address']+'<br>--<br>'+self.get_email_content(customer)['subject']+'<br>--<br>'+self.get_email_content(customer)['body']
        contact.user=User.query.filter_by(username=getpass.getuser()).first()
        contact.direction=u'out'
        contact.type='email'
        contact.customer=customer
        contact.datetime = datetime.datetime.now()

        #return the contact object so that we can store it in the db an open it in a formview
        return contact

    def __unicode__(self):
        return self.label or u'unknown email action'
        
    class Admin(EntityAdmin):
        verbose_name = 'Email Action'
        list_display = ['label', 'description','default_language_template', 'show_in_customer_context',  'create_contact_entry']
        form_display = ['label', 'description', 'icon','default_language_template', 'email_templates','create_contact_entry','contact_entry_summary','contact_entry_text','show_in_customer_context']
        field_attributes=dict(
                              default_language_template=dict(delegate=delegates.Many2OneDelegate)
                              )


